OpenEdge Data Management:<br />DataServer for ODBC Locking examples

The following example illustrates how the end of a transaction affects an OpenEdge database and ODBC data-source locks differently:


DO TRANSACTION: FIND customer WHERE cust-num = 10. UPDATE customer. END. /* Do a second transaction with the same customer record. */

When you access an OpenEdge database with this procedure, the customer record is share-locked when the first transaction ends.

When you access an ODBC data source with the DataServer, the customer record is released when the first transaction ends.

This example illustrates how an OpenEdge database and ODBC data-source shared locks differ in scope and duration:

FIND customer WHERE cust-num = 10 SHARE-LOCK NO-WAIT NO-ERROR. IF AVAILABLE customer THEN DO: DISPLAY customer. PROMPT-FOR customer. tx: DO TRANSACTION ON ERROR UNDO tx, RETRY tx: FIND customer WHERE cust-num = 10 EXCLUSIVE-LOCK NO-WAIT NO-ERROR. IF LOCKED customer THEN DO: Message "customer locked - retrying". UNDO tx, RETRY tx. END. ELSE DO: ASSIGN customer. LEAVE. END. END. END.

FIND customer WHERE cust-num = 10 SHARE-LOCK NO-WAIT NO-ERROR.
IF AVAILABLE customer THEN DO:
  DISPLAY customer.
  PROMPT-FOR customer.
  
  tx:
  DO TRANSACTION ON ERROR UNDO tx, RETRY tx:
    FIND customer WHERE cust-num = 10 EXCLUSIVE-LOCK NO-WAIT NO-ERROR.
    IF LOCKED customer THEN DO:
      Message "customer locked - retrying".
      UNDO tx, RETRY tx.
    END.
    ELSE DO:
      ASSIGN customer.
      LEAVE.
    END.
  END.
END.

In this example, the first record is only share-locked within the ODBC data source if the isolation level setting requires it. (Recall that a share lock specified in a Progress statement is ignored by the DataServer.) As a result, the first record might be updated before the second FIND statement executes, in which case the record that the second FIND statement fetches might be different from the record fetched by the first FIND statement. This procedure might cause update information to be lost because the procedure applies updates based on the first record, and these updates will overwrite the values in the second record.

Using the DataServer to access an ODBC database ensures that locks are upgraded in the data source in the same way as in an OpenEdge database. For example, the following procedure causes the same behavior whether you access an OpenEdge database or an ODBC data source:


FIND customer WHERE cust-num = 10. DISPLAY customer. PROMPT-FOR customer. DO TRANSACTION: ASSIGN customer. END.

The record is share-locked when it is fetched. The DataServer upgrades the shared lock to an exclusive lock inside the transaction by locking the record, reading it, and checking whether the record has changed since it was first fetched. If it has changed, the lock upgrade fails and you receive an error message.

If your data source uses record-level locking, you might have to wait to access a record in the following circumstances:

You try to update a record when another user is reading it (it is share-locked). This also depends on the isolation level.

You try to read or update a record when another user is updating it (it is exclusive-locked).

When this happens, Progress uses a time-out loop, checking periodically to see whether the record is available. You can choose Cancel at any time to abort the request.

The ODBC data source notifies the DataServer if it cannot perform a requested operation within a given period of time. Under unusual system or network loads, the DataServer might receive notification that a request has not been completed. In this case, it returns a message that the record that the request was accessing is locked, even though no other user has a lock on the record.

One type of locking behavior that you might encounter is a deadlock, or “deadly embrace.” A deadlock occurs when two users want to access each other’s table, page, or record, and either the table, page, or record that they want has an exclusive lock on it or they need to put an exclusive lock on it. Neither table, page, or record will give up its lock until the other is available. When an ODBC data source detects this situation:

The data source kills the transaction that has accumulated the least amount of CPU time and releases the table, page, or record for the other user.

Progress displays a message that the transaction was killed.

The system responds as if you had chosen Cancel.

Note: On DB2 for AIX, the DBA must set the variable LOCKTIMEOUT to some number of seconds in the database configuration to get beyond a deadlock condition. The default value (-1) causes a user to wait indefinitely for a lock to be released when attempting to update a locked record.

For details on how Progress locks work, see the OpenEdge Development: Progress 4GL Handbook . See ODBC and data-source documentation for more information about ODBC and data-source locks.